--I think this generates each subcategory average score.
USE [Weval test]
GO

/****** Object:  StoredProcedure [dbo].[ScorebyCategory]    Script Date: 8/7/2013 6:50:36 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ScorebyCategory] @CRN int, @year int, @semester int
AS
BEGIN
	SET NOCOUNT ON;

     --Insert statements for procedure here
	
--DECLARE @CRN int = 30626, @semester int = 3, @year int = 2013, @testID int = 64529;
DECLARE @testID int = 
(
	SELECT DISTINCT TOP 1 T.ID
	FROM Tests T
		INNER JOIN SectionTests ST ON T.ID = ST.testID
		INNER JOIN Results R ON ST.deliveryID = R.deliveryID
		INNER JOIN CourseSections CS ON R.sectionID = CS.ID
			AND CS.bannerCRN = @CRN
			AND CS.year = @year
			AND CS.semester = @semester
			AND typeID = 5
);

WITH cteMain as
(
	SELECT 
		CASE 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly disagree' THEN 0.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'very poor' THEN 0.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'poor' THEN 1.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 2.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'average' THEN 2.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 3.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'good' THEN 3.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 4.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'excellent' THEN 4.0
		END Score,
			C.categoryName CategoryName,
			C.ID CategoryID,
			Q.sequence,
			Q.id

	FROM SectionsTaught ST
			INNER JOIN Results R ON R.sectionID = ST.sectionID
				AND (R.deleted = 0 OR R.deleted IS NULL)
			INNER JOIN CourseSections CS ON CS.ID = R.sectionID
				AND CS.bannerCRN = @CRN
				AND CS.year = @year
				AND CS.semester = @semester
			INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
			INNER JOIN Questions Q ON Q.ID = QR.questionID
			INNER JOIN Tests T ON T.ID = Q.testID 
				AND T.typeID = 5 --Course Eval
				AND T.ID = @TestID
			INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
			INNER JOIN Distractors D ON D.ID = DR.distractorID
			INNER JOIN Categories C on C.ID = Q.categoryID
)
SELECT
	AVG(score) Average,
	STDEVP(score) StandardDeviation,
	CategoryName,
	CategoryID,
	STUFF((
		select DISTINCT ',' + CAST(Q3.sequence AS char(3))
			from (SELECT TOP 100 PERCENT * FROM cteMain Q2 ORDER BY Q2.sequence) AS Q3
			where Q3.CategoryName = Q.CategoryName
			--order by  Q2.sequence, (',' + CAST(Q2.sequence AS char(3)))
			for xml path('Q3'), type).value('.','nvarchar(max)'),1,1,'') Questions
FROM 
	cteMain Q
GROUP BY CategoryName, CategoryID
ORDER BY MIN(Q.sequence), Q.CategoryID

END

GO

